package com.glowdayz.glowmee.dao;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.MultipartBox;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.Util;
import com.glowdayz.glowmee.cmd.RegisterCMD;

/** 회원/관리자 계정의 기능을 구현하는 클래스 */
public class RegisterDAO {
	
	static Logger logger = Logger.getLogger(RegisterDAO.class);
	
	private static RegisterDAO instance = null;
	
	DBHandler db = null;
	
	public static RegisterDAO getInstance() {
		if (instance == null) {
			instance = new RegisterDAO();
		}
		return instance;
	}
	
	public RegisterDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 회원의 가입으로 인한 추가
	 * @param cmd
	 * @return int
	 */
	public int insertRegister(RegisterCMD cmd) throws Exception {
		logger.debug("[insertRegister] ---------- Method Start ----------");
		logger.debug("[insertRegister][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "INSERT INTO register("
				+ "userType, eMail, passWord, nickName, isActivity, userImg, regId, recommender, create_date"
				+ ") VALUES ('USER', ?, password(?), ?, 1, 1, ?, ?, ?);";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.geteMail());
			pstmt.setString(2, cmd.getPassWord());
			pstmt.setString(3, cmd.getNickName());
			pstmt.setString(4, cmd.getRegId());
			if (cmd.getRecommender() < 1) {
				pstmt.setNull(5, Types.INTEGER);
			} else {
				pstmt.setInt(5, cmd.getRecommender());
			}
			pstmt.setString(6, DateTime.getInstance().getNowDateTimeString());
			
			DBHandler.getInstance().beginTrans();
			int count = pstmt.executeUpdate();
			
			if (count > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertRegister] " + e.getLocalizedMessage());
			logger.error("[insertRegister] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertRegister][RETURN] result=" + result);
		logger.debug("[insertRegister] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 추천인 검색
	 * @param where
	 * @return int
	 */
	public int getRecommender(String where) throws SQLException {
		logger.debug("[getRecommender] ---------- Method Start ----------");
		logger.debug("[getRecommender][PARAM] where=" + where);
		
		int result = 0;
		
		String sql = "SELECT idRegister FROM register WHERE nickName=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, where);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if(rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRecommender] " + e.getLocalizedMessage());
			logger.error("[getRecommender] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		logger.debug("[getRecommender] ---------- Method End ----------");
		return result;
	}
	
	/**
	 * 회원의 가입시 추가정보 입력
	 * @param cmd
	 * @param imageList
	 * @return int
	 */
	public int insertAddRegister(RegisterCMD cmd, FileInfo image) throws Exception {
		logger.debug("[insertAddRegister] ---------- Method Start ----------");
		logger.debug("[insertAddRegister][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		if (image != null) {
			try {
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, isUser, create_date"
						+ ") VALUES ('register', ?, ?, ?, ?, ?, ?, 1, 1, ?);";
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, cmd.getIdRegister());
				pstmt.setString(2, image.getFileOrgName());
				pstmt.setString(3, image.getFileSaveName());
				pstmt.setString(4, image.getFileDir());
				pstmt.setDouble(5, image.getFileSize());
				pstmt.setString(6, image.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				int count = pstmt.executeUpdate();
				
				if (count > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						result = rs.getInt(1);
					}
					rs.close();
					rs = null;
				}
				pstmt.close();
				pstmt = null;
				
				if (cmd.getRegId() == null || cmd.getRegId().equals("")) {
					String sql = "UPDATE register "
							+ "SET gender=?, birthYear=?, skinType=?, location=?, userImg=?, modified_date=? "
							+ "WHERE idRegister=?";
					pstmt = db.getPrepareStatement(sql);
					pstmt.setInt(1, cmd.getGender());
					pstmt.setInt(2, cmd.getBirthYear());
					pstmt.setInt(3, cmd.getSkinType());
					pstmt.setInt(4, cmd.getLocation());
					pstmt.setInt(5, result);
					pstmt.setString(6, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(7, cmd.getIdRegister());
					
					result = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} else {
					String sql = "UPDATE register "
							+ "SET gender=?, birthYear=?, skinType=?, location=?, userImg=?, regId=?, modified_date=? "
							+ "WHERE idRegister=?";
					pstmt = db.getPrepareStatement(sql);
					pstmt.setInt(1, cmd.getGender());
					pstmt.setInt(2, cmd.getBirthYear());
					pstmt.setInt(3, cmd.getSkinType());
					pstmt.setInt(4, cmd.getLocation());
					pstmt.setInt(5, result);
					pstmt.setString(6, cmd.getRegId());
					pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(8, cmd.getIdRegister());
					
					result = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[insertAddRegister] " + e.getLocalizedMessage());
				logger.error("[insertAddRegister] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		} else {
			try {
				String sql = "UPDATE register "
						+ "SET gender=?, birthYear=?, skinType=?, location=?, modified_date=? "
						+ "WHERE idRegister=?";
				pstmt = db.getPrepareStatement(sql);
				pstmt.setInt(1, cmd.getGender());
				pstmt.setInt(2, cmd.getBirthYear());
				pstmt.setInt(3, cmd.getSkinType());
				pstmt.setInt(4, cmd.getLocation());
				pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
				pstmt.setInt(6, cmd.getIdRegister());
				
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[insertAddRegister] " + e.getLocalizedMessage());
				logger.error("[insertAddRegister] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertAddRegister][RETURN] result=" + result);
		logger.debug("[insertAddRegister] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 회원의 수정시 추가정보 입력
	 * @param cmd
	 * @param imageList
	 * @return int
	 */
	public int updateAddRegister(RegisterCMD cmd, FileInfo finfo) throws Exception {
		logger.debug("[updateAddRegister] ---------- Method Start ----------");
		logger.debug("[updateAddRegister][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		FileInfo image = this.getImage(cmd.getIdRegister());
		if (image != null && finfo != null) {
			try {
				String edit = "UPDATE attachefile SET fileOrgName=?, fileSaveName=?, fileDir=?, fileSize=?, fileType=?, modified_date=? "
						+ "WHERE idAttacheFile=?";
				pstmt = db.getPrepareStatement(edit);
				pstmt.setString(1, finfo.getFileOrgName());
				pstmt.setString(2, finfo.getFileSaveName());
				pstmt.setString(3, finfo.getFileDir());
				pstmt.setLong(4, finfo.getFileSize());
				pstmt.setString(5, finfo.getFileType());
				pstmt.setString(6, DateTime.getInstance().getNowDateTimeString());
				pstmt.setInt(7, image.getIdAttacheFile());
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
				finfo = null;
				File f = new File(image.getFileDir() + "/" + image.getFileSaveName());
				if (f.exists()) {
					f.delete();
				}
				
				String sql = "UPDATE register "
						+ "SET gender=?, birthYear=?, skinType=?, location=?, modified_date=? "
						+ "WHERE idRegister=?";
				pstmt = db.getPrepareStatement(sql);
				pstmt.setInt(1, cmd.getGender());
				pstmt.setInt(2, cmd.getBirthYear());
				pstmt.setInt(3, cmd.getSkinType());
				pstmt.setInt(4, cmd.getLocation());
				pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
				pstmt.setInt(6, cmd.getIdRegister());
				
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[updateAddRegister] " + e.getLocalizedMessage());
				logger.error("[updateAddRegister] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		} else {
			try {
				String sql = "UPDATE register "
						+ "SET gender=?, birthYear=?, skinType=?, location=?, modified_date=? "
						+ "WHERE idRegister=?";
				pstmt = db.getPrepareStatement(sql);
				pstmt.setInt(1, cmd.getGender());
				pstmt.setInt(2, cmd.getBirthYear());
				pstmt.setInt(3, cmd.getSkinType());
				pstmt.setInt(4, cmd.getLocation());
				pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
				pstmt.setInt(6, cmd.getIdRegister());
				
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[updateAddRegister] " + e.getLocalizedMessage());
				logger.error("[updateAddRegister] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateAddRegister][RETURN] result=" + result);
		logger.debug("[updateAddRegister] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 회원의 프로필 수정
	 * @param cmd
	 * @return int
	 */
	public int updateRegisterProfile(RegisterCMD cmd) throws Exception {
		logger.debug("[updateRegisterProfile] ---------- Method Start ----------");
		logger.debug("[updateRegisterProfile][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE register "
				+ "SET nickName=?, location=?, birthYear=?, skinType=?, gender=?, userImg=?, modified_date=? "
				+ "WHERE idRegister=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getNickName());
			pstmt.setInt(2, cmd.getLocation());
			pstmt.setInt(3, cmd.getBirthYear());
			pstmt.setInt(4, cmd.getSkinType());
			pstmt.setInt(5, cmd.getGender());
			pstmt.setInt(6, cmd.getUserImg());
			pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(8, cmd.getIdRegister());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateRegisterProfile] " + e.getLocalizedMessage());
			logger.error("[updateRegisterProfile] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateRegisterProfile][RETURN] result=" + result);
		logger.debug("[updateRegisterProfile] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 회원의 비밀번호 수정
	 * @param cmd
	 * @return int
	 */
	public int updateRegisterPassword(RegisterCMD cmd) throws Exception {
		logger.debug("[updateRegisterPassword] ---------- Method Start ----------");
		logger.debug("[updateRegisterPassword][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE register "
				+ "SET passWord=password(?), modified_date=? "
				+ "WHERE idRegister=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getPassWord());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdRegister());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateRegisterPassword] " + e.getLocalizedMessage());
			logger.error("[updateRegisterPassword] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateRegisterPassword][RETURN] result=" + result);
		logger.debug("[updateRegisterPassword] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 회원/관리자 비활성용
	 * @param cmd
	 * @return int
	 */
	public int unActivityRegister(RegisterCMD cmd) throws Exception {
		logger.debug("[unActivityRegister] ---------- Method Start ----------");
		logger.debug("[unActivityRegister][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE register "
				+ "SET isActivity=?, modified_date=? "
				+ "WHERE idRegister=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsActivity());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdRegister());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[unActivityRegister] " + e.getLocalizedMessage());
			logger.error("[unActivityRegister] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[unActivityRegister][RETURN] result=" + result);
		logger.debug("[unActivityRegister] ---------- Method End ----------");
		
		return result;
	}
	
	public int updateRegId(int idRegister, String regId) throws Exception {
		logger.debug("[unActivityRegister] ---------- Method Start ----------");
		
		int result = 0;
		
		String sql = "UPDATE register "
				+ "SET regId=? "
				+ "WHERE idRegister=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, regId);
			pstmt.setInt(2, idRegister);
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[unActivityRegister] " + e.getLocalizedMessage());
			logger.error("[unActivityRegister] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[unActivityRegister][RETURN] result=" + result);
		logger.debug("[unActivityRegister] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 관리자페이지에서의 회원수정
	 * @param cmd
	 * @return int
	 */
	public int updateRegisterOfAdmin(RegisterCMD cmd, String[] delete_file) throws Exception {
		logger.debug("[updateRegisterOfAdmin] ---------- Method Start ----------");
		logger.debug("[updateRegisterOfAdmin][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		
		// (1) 삭제할 파일에 대한 리스트를 불러온다.
		ArrayList<FileInfo> delete_file_list = null;
		
		if (delete_file != null) {
			String in_cause = "(";
			
			for (int i=0; i<delete_file.length; i++) {
				in_cause += "'" + delete_file[i] + "'";
				
				if (i + 1 < delete_file.length) {
					in_cause += ",";
				}
			}
			
			in_cause += ")";
			
			// 삭제 대상을 불러올 SQL
			String fileSql = "SELECT fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM attachefile "
					+ "WHERE tableName='register' AND tableKey=? AND fileSaveName in " + in_cause;
			pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
			
			try {
				pstmt.setInt(1, cmd.getIdRegister());
				ResultSet fileRs = pstmt.executeQuery();
				if (fileRs != null) {
					// 삭제 대상을 만들기 위한 ArrayList 작성
					delete_file_list = new ArrayList<FileInfo>();
					if (fileRs.next()) {
						String fileOrgName = fileRs.getString(1);
						String fileSaveName = fileRs.getString(2);
						String fileDir = fileRs.getString(3);
						int fileSize = fileRs.getInt(4);
						String fileType = fileRs.getString(5);
						
						FileInfo info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
						logger.debug("delete_fileInfo : " + info.toString());
						delete_file_list.add(info);
					}
					fileRs.close();
					fileRs = null;
				}
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[updateRegisterOfAdmin] " + e.getLocalizedMessage());
				logger.error("[updateRegisterOfAdmin] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
			
			String update = "UPDATE register "
					+ "SET userImg=null "
					+ "WHERE idRegister=?";
			pstmt = db.getPrepareStatement(update);
			pstmt.setInt(1, cmd.getIdRegister());
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
				
			// 게시물 데이터 삭제처리
			String delSql = "DELETE FROM attachefile "
					+ "WHERE tableName='register' AND tableKey=? AND fileSaveName in " + in_cause;
			
			pstmt = DBHandler.getInstance().getPrepareStatement(delSql);
			try {
				pstmt.setInt(1, cmd.getIdRegister());
				
				pstmt.executeUpdate();
				
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[updateRegisterOfAdmin] " + e.getLocalizedMessage());
				logger.error("[updateRegisterOfAdmin] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		// (2) 삭제 대상에 대한 처리
		if (delete_file_list != null) {
			MultipartBox mbox = MultipartBox.getInstance();
			mbox.deleteFiles(delete_file_list);
		}
		
		// (3) 게시물 정보 갱신
		String sql = "UPDATE register "
				+ "SET isActivity=?, modified_date=? "
				+ "WHERE idRegister=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsActivity());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdRegister());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateRegisterOfAdmin] " + e.getLocalizedMessage());
			logger.error("[updateRegisterOfAdmin] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateRegisterOfAdmin][RETURN] result=" + result);
		logger.debug("[updateRegisterOfAdmin] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 관리자 계정추가
	 * @param cmd
	 * @return int
	 */
	public int insertAdmin(RegisterCMD cmd) throws Exception {
		logger.debug("[insertAdmin] ---------- Method Start ----------");
		logger.debug("[insertAdmin][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "INSERT INTO register("
				+ "userType, eMail, passWord, nickName, isActivity, adminLevel, create_date"
				+ ") VALUES ('ADMIN', ?, password(?), ?, 1, ?, ?);";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.geteMail());
			pstmt.setString(2, cmd.getPassWord());
			pstmt.setString(3, cmd.getNickName());
			pstmt.setInt(4, cmd.getAdminLevel());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			
			DBHandler.getInstance().beginTrans();
			int count = pstmt.executeUpdate();
			
			if (count > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertAdmin] " + e.getLocalizedMessage());
			logger.error("[insertAdmin] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertAdmin][RETURN] result=" + result);
		logger.debug("[insertAdmin] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 회원 조회
	 * @param idRegister
	 * @return RegisterCMD
	 */
	public RegisterCMD getRegister(int idRegister) throws Exception {
		logger.debug("[getRegister] ---------- Method Start ----------");
		logger.debug("[getRegister][PARAM] idRegister=" + idRegister);
		
		RegisterCMD result = null;
		String sql = "SELECT r.idRegister, r.eMail, r.passWord, r.nickName, r.location, lp.commonText, l.commonText, r.birthYear, r.skinType, "
				+ "s.commonText, r.gender, g.commonText, r.isActivity, r.userImg, r.recommender, c.nickName, r.create_date, r.modified_date, "
				+ "(SELECT count(*) FROM pouch WHERE idRegister=?) cnt1, (SELECT count(*) FROM reviewcomment WHERE idRegister=?) cnt2, "
				+ "(SELECT count(*) FROM wishlistmapping WHERE idRegister=?) cnt3, (SELECT count(*) FROM register WHERE recommender=?) cnt4 "
				+ "FROM register r LEFT OUTER JOIN commoncode l ON r.location=l.idCommon LEFT OUTER JOIN commoncode lp ON lp.idCommon=l.parentId "
				+ "LEFT OUTER JOIN commoncode s ON r.skinType=s.idCommon LEFT OUTER JOIN commoncode g ON r.gender=g.idCommon "
				+ "LEFT OUTER JOIN register c ON r.recommender=c.idRegister WHERE r.idRegister=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			pstmt.setInt(2, idRegister);
			pstmt.setInt(3, idRegister);
			pstmt.setInt(4, idRegister);
			pstmt.setInt(5, idRegister);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					String eMail = rs.getString(2);
					String passWord = rs.getString(3);
					String nickName = rs.getString(4);
					int location = rs.getInt(5);
					String locationValue1 = rs.getString(6);
					String locationValue2 = rs.getString(7);
					int birthYear = rs.getInt(8);
					int skinType = rs.getInt(9);
					String skinTypeValue = rs.getString(10);
					int gender = rs.getInt(11);
					String genderValue = rs.getString(12);
					int isActivity = rs.getInt(13);
					int userImg = rs.getInt(14);
					int recommender = rs.getInt(15);
					String recommenderValue = rs.getString(16);
					String create_date = rs.getString(17);
					String modified_date = rs.getString(18);
					int pouchCount = rs.getInt(19);
					int reviewCount = rs.getInt(20);
					int recommentCount = rs.getInt(21);
					int wishCount = rs.getInt(22);
					
					FileInfo image = this.getImage(num);
					
					result = new RegisterCMD(num, eMail, passWord, nickName, location, locationValue1, locationValue2,
							birthYear, skinType, skinTypeValue, gender, genderValue, isActivity, userImg, image,
							recommender, recommenderValue, create_date, modified_date, pouchCount, reviewCount, recommentCount, wishCount);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRegister] " + e.getLocalizedMessage());
			logger.error("[getRegister] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getRegister][RETURN] result=" + result.toString());
		}
		logger.debug("[getRegister] ---------- Method End ----------");
		
		return result;
	}
	
	public String getRegisterNickName(int idRegister) throws SQLException {
		String result = null;
		
		String sql = "SELECT nickName FROM register WHERE idRegister=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					result = rs.getString(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRegisterNickName] " + e.getLocalizedMessage());
			logger.error("[getRegisterNickName] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return result;
	}
	
	public String getRegId(int idRegister) throws SQLException {
		String result = null;
		
		String sql = "SELECT regId FROM register WHERE idRegister=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					result = rs.getString(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRegId] " + e.getLocalizedMessage());
			logger.error("[getRegId] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return result;
	}
	
	public RegisterCMD getRegisterXml(int idRegister) throws Exception {
		logger.debug("[getRegister] ---------- Method Start ----------");
		logger.debug("[getRegister][PARAM] idRegister=" + idRegister);
		
		RegisterCMD result = null;
		String sql = "SELECT r.idRegister, r.eMail, r.nickName, lp.commonText, l.commonText, r.birthYear, "
				+ "s.commonText, g.commonText, r.userTel, r.userAddress, "
				+ "(SELECT COUNT(*) FROM pouch p WHERE r.idRegister=p.idRegister AND p.isDisplay=1), "
				+ "(SELECT COUNT(*) FROM wishuserpouchmapping m WHERE r.idRegister=m.idRegister), "
				+ "(SELECT COUNT(*) FROM reviewcomment rc WHERE r.idRegister=rc.idRegister AND rc.isDisplay=1) "
				+ "FROM register r LEFT OUTER JOIN commoncode l ON r.location=l.idCommon LEFT OUTER JOIN commoncode lp ON lp.idCommon=l.parentId "
				+ "LEFT OUTER JOIN commoncode s ON r.skinType=s.idCommon LEFT OUTER JOIN commoncode g ON r.gender=g.idCommon "
				+ "LEFT OUTER JOIN register c ON r.recommender=c.idRegister WHERE r.idRegister=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					String eMail = rs.getString(2);
					String nickName = rs.getString(3);
					String locationValue1 = rs.getString(4);
					String locationValue2 = rs.getString(5);
					int birthYear = rs.getInt(6);
					String skinTypeValue = rs.getString(7);
					String genderValue = rs.getString(8);
					String userTel = rs.getString(9);
					String userAddress = rs.getString(10);
					int pouchCount = rs.getInt(11);
					int userPouchCount = rs.getInt(12);
					int reviewCount = rs.getInt(13);
					
					FileInfo image = this.getImage(num);
					
					result = new RegisterCMD(idRegister, eMail, nickName, locationValue1, locationValue2, birthYear, 
							skinTypeValue, genderValue, image, userTel, userAddress, pouchCount, reviewCount, userPouchCount);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRegister] " + e.getLocalizedMessage());
			logger.error("[getRegister] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getRegister][RETURN] result=" + result.toString());
		}
		logger.debug("[getRegister] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 회원/관리자 리스트
	 * @param page
	 * @param count
	 * @param isUser
	 * @param order
	 * @param where
	 * @return ArrayList<RegisterCMD>
	 */
	public ArrayList<RegisterCMD> getRegisterList(int page, int count, String isUser, String order, String where) throws Exception {
		logger.debug("[getRegisterList] ---------- Method Start ----------");
		logger.debug("[getRegisterList][PARAM] page=" + page);
		logger.debug("[getRegisterList][PARAM] count=" + count);
		logger.debug("[getRegisterList][PARAM] isUser=" + isUser);
		logger.debug("[getRegisterList][PARAM] order=" + order);
		logger.debug("[getRegisterList][PARAM] where=" + where);
		
		ArrayList<RegisterCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("register", "idRegister", "userType="  + "'" + isUser + "'");
		} else {
			cnt = db.getCount("register", "idRegister", "userType="  + "'" + isUser + "'" + where);
		}
		
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT idRegister, userType, eMail, passWord, nickName, location, birthYear, skinType, gender, "
					+ "isActivity, userImg, recommender, adminLevel, create_date, modified_date "
					+ "FROM register WHERE userType=? "
					+ "ORDER BY create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT idRegister, userType, eMail, passWord, nickName, location, birthYear, skinType, gender, "
					+ "isActivity, userImg, recommender, adminLevel, create_date, modified_date "
					+ "FROM register WHERE userType=?" + where + " "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT idRegister, userType, eMail, passWord, nickName, location, birthYear, skinType, gender, "
					+ "isActivity, userImg, recommender, adminLevel, create_date, modified_date "
					+ "FROM register WHERE userType=? "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT idRegister, userType, eMail, passWord, nickName, location, birthYear, skinType, gender, "
					+ "isActivity, userImg, recommender, adminLevel, create_date, modified_date "
					+ "FROM register WHERE userType=?" + where + " "
					+ "ORDER BY create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setString(1, isUser);
			pstmt.setInt(2, PageHandler.getInstance().getStartRec());
			pstmt.setInt(3, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<RegisterCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					String userType = rs.getString(2);
					String eMail = rs.getString(3);
					String passWord = rs.getString(4);
					String nickName = rs.getString(5);
					int location = rs.getInt(6);
					int birthYear = rs.getInt(7);
					int skinType = rs.getInt(8);
					int gender = rs.getInt(9);
					int isActivity = rs.getInt(10);
					int userImg = rs.getInt(11);
					int recommender = rs.getInt(12);
					int adminLevel = rs.getInt(13);
					String create_date = rs.getString(14);
					String modified_date = rs.getString(15);
					RegisterCMD item = new RegisterCMD(num, userType, eMail, passWord, nickName, location, birthYear, skinType, gender, 
							isActivity, userImg, recommender, adminLevel, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRegisterList] " + e.getLocalizedMessage());
			logger.error("[getRegisterList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getRegisterList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getRegisterList] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 이미지
	 * @param idx
	 * @return FileInfo
	 */
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql;
		
		if (idx == -1) {
			fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM attachefile "
					+ "WHERE fileSaveName='noimage.png' AND fileDir= '/upload'";
		} else {
			fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM attachefile "
					+ "WHERE tableName='register' AND tableKey=" + idx;
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo image = null;
		
		try {
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					image = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return image;
	}
	
	/**
	 * 로그인
	 * @param admin_id
	 * @param admin_pw
	 * @return
	 */
	public RegisterCMD doLogin(String admin_id, String admin_pw) throws SQLException {
		logger.debug("[doLogin] ---------- Method Start ----------");
		logger.debug("[doLogin][PARAM] admin_id=" + admin_id);
		logger.debug("[doLogin][PARAM] admin_pw=" + admin_pw);
		
		RegisterCMD item = null;
		
		String sql = "SELECT idRegister, userType, eMail, passWord, nickName, isActivity, adminLevel, create_date, modified_date "
				+ "FROM register "
				+ "WHERE eMail=? AND passWord=password(?) AND isActivity=1";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, admin_id);
			pstmt.setString(2, admin_pw);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				if (rs.next()) {
					int idRegister = rs.getInt(1);
					String userType = rs.getString(2);
					String eMail = rs.getString(3);
					String passWord = rs.getString(4);
					String nickName = rs.getString(5);
					int isActivity = rs.getInt(6);
					int adminLevel = rs.getInt(7);
					String create_date = rs.getString(8);
					String modified_date = rs.getString(9);
					
					item = new RegisterCMD(idRegister, userType, eMail, passWord, nickName, isActivity, adminLevel, create_date, modified_date);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		logger.debug("[doLogin] ---------- Method End ----------");
		
		return item;
	}
	
	/**
	 * 비밀번호 요청
	 * @param eMail
	 * @return
	 */
	public String requestPw(String eMail) throws SQLException {
		logger.debug("[requestPw] ---------- Method Start ----------");
		logger.debug("[requestPw][PARAM] eMail=" + eMail);
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		int random1 = Util.getInstance().random(1, 9);
		int random2 = Util.getInstance().random(0, 9);
		int random3 = Util.getInstance().random(0, 9);
		int random4 = Util.getInstance().random(0, 9);
		int random5 = Util.getInstance().random(0, 9);
		int random6 = Util.getInstance().random(0, 9);
		
		String password = "" + random1 + random2 + random3 + random4 + random5 + random6;
		
		String sql = "UPDATE register "
				+ "SET passWord=password(?), modified_date=? "
				+ "WHERE eMail=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, password);
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setString(3, eMail);
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateRegisterProfile] " + e.getLocalizedMessage());
			logger.error("[updateRegisterProfile] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[requestPw] ---------- Method End ----------");
		
		return password;
	}
	
	/** 추가정보 입력 xml */
	public int moreInfoRegister(RegisterCMD cmd) throws SQLException {
		logger.debug("[moreInfoRegister] ---------- Method Start ----------");
		logger.debug("[moreInfoRegister][PARAM] RegisterCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE register "
				+ "SET userTel=?, userAddress=?, modified_date=? "
				+ "WHERE idRegister=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getUserTel());
			pstmt.setString(2, cmd.getUserAddress());
			pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(4, cmd.getIdRegister());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[moreInfoRegister] " + e.getLocalizedMessage());
			logger.error("[moreInfoRegister] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[moreInfoRegister][RETURN] result=" + result);
		logger.debug("[moreInfoRegister] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품 위시 xml */
	public int wishListProduct(int idRegister, int idProduct) throws SQLException {
		logger.debug("[wishListProduct] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		String sql = "INSERT INTO wishlistmapping("
				+ "idRegister, idProduct, create_date"
				+ ") VALUES (?, ?, ?);";
		
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			pstmt.setInt(2, idProduct);
			pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[wishListProduct] " + e.getLocalizedMessage());
			logger.error("[wishListProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int readCnt = 0, ratingCnt = 0, wishCnt = 0, pouchCnt = 0;
		double ratingAvg = 0.0;
		String selectSql = "SELECT p.readCount, p.ratingAvg, p.rationCount, "
				+ "(SELECT COUNT(*) FROM wishlistmapping m WHERE m.idProduct=p.idProduct), "
				+ "(SELECT COUNT(*) FROM pouchproductmapping pm WHERE pm.idProduct=p.idProduct) "
				+ "FROM product p "
				+ "WHERE p.idProduct=?;";
		pstmt = DBHandler.getInstance().getPrepareStatement(selectSql);
		try {
			pstmt.setInt(1, idProduct);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					readCnt = rs.getInt(1);
					ratingAvg = rs.getDouble(2);
					ratingCnt = rs.getInt(3);
					wishCnt = rs.getInt(4);
					pouchCnt = rs.getInt(5);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[getEditerPouch] " + e.getLocalizedMessage());
			logger.error("[getEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int productScore = (readCnt * 1) + ((int) (ratingAvg * ratingCnt)) + (wishCnt * 5) + (pouchCnt * 10);
		
		String updateSql = "UPDATE product "
				+ "SET productScore=? "
				+ "WHERE idProduct=?;";
		
		pstmt = db.getPrepareStatement(updateSql);
		
		try {
			pstmt.setInt(1, productScore);
			pstmt.setInt(2, idProduct);
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[wishListPouch] " + e.getLocalizedMessage());
			logger.error("[wishListPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[wishListProduct][RETURN] result=" + result);
		logger.debug("[wishListProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/** 파우치 위시 xml */
	public int wishListPouch(int idRegister, int idPouch) throws SQLException {
		logger.debug("[wishListPouch] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		String sql = "INSERT INTO wishuserpouchmapping("
				+ "idRegister, idPouch, create_date"
				+ ") VALUES (?, ?, ?);";
		
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			pstmt.setInt(2, idPouch);
			pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[wishListPouch] " + e.getLocalizedMessage());
			logger.error("[wishListPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int readCnt = 0, recommendCnt = 0, commentCnt = 0;
		
		String selectSql = "SELECT p.readCount, p.recommendCount, (SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) "
				+ "FROM pouch p "
				+ "WHERE p.idPouch=?;";
		pstmt = DBHandler.getInstance().getPrepareStatement(selectSql);
		try {
			pstmt.setInt(1, idPouch);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					readCnt = rs.getInt(1);
					recommendCnt = rs.getInt(2);
					commentCnt = rs.getInt(3);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[getEditerPouch] " + e.getLocalizedMessage());
			logger.error("[getEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int pouchScore = (readCnt * 1) + ((recommendCnt + 1) * 10) + (commentCnt * 5);
		
		String updateSql = "UPDATE pouch "
				+ "SET recommendCount=(recommendCount+1), pouchScore=? "
				+ "WHERE idPouch=?;";
		
		pstmt = db.getPrepareStatement(updateSql);
		
		try {
			pstmt.setInt(1, pouchScore);
			pstmt.setInt(2, idPouch);
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[wishListPouch] " + e.getLocalizedMessage());
			logger.error("[wishListPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[wishListPouch][RETURN] result=" + result);
		logger.debug("[wishListPouch] ---------- Method End ----------");
		
		return result;
	}

}
